/**
 * 
 */
package org.swing.utility.jdbc.base.dao;

import java.io.ByteArrayInputStream;
import java.io.Reader;
import java.lang.reflect.Field;
import java.lang.reflect.Modifier;
import java.lang.reflect.ParameterizedType;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.sql.DataSource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.swing.utility.jdbc.connection.ConnectionProvider;
import org.swing.utility.jdbc.transaction.Transaction;

/**
 * @author lqnhu
 *
 */
public abstract class AbstractBaseDAO<T> implements IBaseDAO<T> {
	private static Logger LOG = LoggerFactory.getLogger(AbstractBaseDAO.class);
	private static String PAGE_SQL = "select * from ( %s ) A limit %d,%d";
	private static String COUNT_SQL = "SELECT count(1) AS CNT FROM (%s) A";
	public static String BOOL_TRUE = "Y";
	private Connection conn;
	private Class<T> clazz;
	@SuppressWarnings("unchecked")
	public AbstractBaseDAO() {
		clazz = (Class<T>) ((ParameterizedType) getClass()
				.getGenericSuperclass()).getActualTypeArguments()[0];
	}

	public int insertItem(T item, boolean returnAutoIncrement)
			throws DAOException {
		conn = this.getConnFromDS();
		try {
			return this.doAddItem(item, returnAutoIncrement);
		} finally {
			this.closeRes(conn, null, null);
		}
	}

	public int insertItem(Transaction trans, T item, boolean returnAutoIncrement)
			throws DAOException {
		conn = getConnFromTrx(trans);
		return this.doAddItem(item, returnAutoIncrement);
	}

	private int doAddItem(T item, boolean returnAutoIncrement)
			throws DAOException {
		StringBuilder insert = new StringBuilder();
		List<Object> values = new ArrayList<Object>();
		buildInsertSQL(item, insert, values);
		LOG.info("doAddItem：" + insert.toString());
		LOG.info("doAddItem:" + values.toString());
		if (conn == null) {
			throw new DAOException("doAddItem : ");
		}
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(insert.toString(),
					PreparedStatement.RETURN_GENERATED_KEYS);
			for (int i = 0; i < values.size(); i++) {
				this.setPrepareStatement(ps, i + 1, values.get(i));
			}
			int ret = ps.executeUpdate();
			if (returnAutoIncrement) {
				rs = ps.getGeneratedKeys();
				rs.next();
				ret = rs.getInt(1);
			}
			return ret;
		} catch (SQLException se) {
			throw new DAOException("执行insert语句失败.", se);
		} finally {
			this.closeRes(null, ps, rs);
		}
	}

	private int buildInsertSQL(T item, StringBuilder insertSQL,
			List<Object> values) throws DAOException {
		if (insertSQL == null || insertSQL.length() != 0 || values == null
				|| values.size() != 0) {
			throw new IllegalArgumentException(
					"buildInsertSQL.");
		}
		try {
			int id = 0;
			insertSQL.append("INSERT into ").append(this.tableName())
					.append("(");
			StringBuilder params = new StringBuilder().append(" values(");
			Field[] fields = clazz.getDeclaredFields();
			boolean first = true;
			for (Field field : fields) {
				if (Modifier.isStatic(field.getModifiers())) {
					continue;
				}
				if (field.isAnnotationPresent(Transient.class)) {
					continue;
				}
				field.setAccessible(true);
				String fieldName = field.getName();
				if ("id".equals(fieldName)) {
					values.add(null);
				} else {
					Object value = field.get(item);
					if (isPrimitive(field.getType())) {
						if (value instanceof Boolean) {
							values.add((Boolean) value ? "Y" : "N");
						} else {
							values.add(value);
						}
					} else {
						continue;
					}
				}
				if (first) {
					insertSQL.append(fieldName);
					params.append("?");
					first = false;
				} else {
					insertSQL.append(",").append(fieldName);
					params.append(",?");
				}
			}
			insertSQL.append(")").append(params.append(")"));
			return id;
		} catch (IllegalAccessException illegal) {
			throw new DAOException("buildInsertSQL.", illegal);
		}
	}

	public int count(Condition[] conds) throws DAOException {
		StringBuilder query = new StringBuilder();
		List<Object> params = new ArrayList<Object>();
		this.buildQuery(conds, query, params);
		conn = this.getConnFromDS();
		try {
			return this.doNamedCount(query.toString(), params.toArray());
		} finally {
			this.closeRes(conn, null, null);
		}
	}

	public int count(Transaction trans, Condition[] conds) throws DAOException {
		StringBuilder query = new StringBuilder();
		List<Object> params = new ArrayList<Object>();
		this.buildQuery(conds, query, params);
		conn = this.getConnFromTrx(trans);
		return this.doNamedCount(query.toString(), params.toArray());
	}

	public int namedCount(String query, Object[] values) throws DAOException {
		conn = this.getConnFromDS();
		try {
			return this.doNamedCount(query, values);
		} finally {
			this.closeRes(conn, null, null);
		}
	}

	public int namedCount(Transaction trans, String query, Object[] values)
			throws DAOException {
		conn = this.getConnFromTrx(trans);
		return this.doNamedCount(query, values);
	}

	private int doNamedCount(String query, Object[] params) throws DAOException {
		String countSQL = this.countingSQL(query);
		LOG.info("doNamedCount：" + countSQL);
		LOG.info("doNamedCount:" + join(params, ","));
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(countSQL);
			if (params != null && params.length != 0) {
				for (int i = 0; i < params.length; i++) {
					this.setPrepareStatement(ps, i + 1, params[i]);
				}
			}
			rs = ps.executeQuery();
			rs.next();
			return rs.getInt(1);
		} catch (SQLException e) {
			throw new DAOException("doNamedCount.", e);
		} finally {
			this.closeRes(null, ps, rs);
		}
	}

	private String countingSQL(String query) {
		return String.format(COUNT_SQL, query);
	}

	public T queryItem(Condition[] conds) throws DAOException {
		List<T> list = this.queryAllItems(conds);
		if (list != null && !list.isEmpty()) {
			return list.get(0);
		} else {
			return null;
		}
	}

	public T queryItem(Transaction trans, Condition[] conds)
			throws DAOException {
		List<T> list = this.queryAllItems(trans, conds);
		if (list != null && !list.isEmpty()) {
			return list.get(0);
		} else {
			return null;
		}
	}

	public List<T> queryItemsByPage(Condition[] conds, int start, int size,
			OrderBy... orderBys) throws DAOException {
		StringBuilder query = new StringBuilder();
		List<Object> values = new ArrayList<Object>();
		this.buildQuery(conds, query, values, orderBys);
		conn = this.getConnFromDS();
		try {
			return this.doNamedQuery(
					this.pagingSQL(query.toString(), start, size),
					values.toArray());
		} finally {
			this.closeRes(conn, null, null);
		}
	}

	public List<T> queryItemsByPage(Transaction trans, Condition[] conds,
			int start, int size, OrderBy... orderBys) throws DAOException {
		StringBuilder query = new StringBuilder();
		List<Object> values = new ArrayList<Object>();
		this.buildQuery(conds, query, values, orderBys);
		conn = this.getConnFromTrx(trans);
		return this.doNamedQuery(this.pagingSQL(query.toString(), start, size),
				values.toArray());
	}

	public List<T> queryAllItems(Condition[] conds, OrderBy... orderBys)
			throws DAOException {
		StringBuilder query = new StringBuilder();
		List<Object> values = new ArrayList<Object>();
		this.buildQuery(conds, query, values, orderBys);
		conn = this.getConnFromDS();
		try {
			return this.doNamedQuery(query.toString(), values.toArray());
		} finally {
			this.closeRes(conn, null, null);
		}
	}

	public List<T> queryAllItems(Transaction trans, Condition[] conds,
			OrderBy... orderBys) throws DAOException {
		StringBuilder query = new StringBuilder();
		List<Object> values = new ArrayList<Object>();
		this.buildQuery(conds, query, values, orderBys);
		conn = getConnFromTrx(trans);
		return this.doNamedQuery(query.toString(), values.toArray());
	}

	public T namedQuery(String query, Object[] values) throws DAOException {
		List<T> list = this.namedQueryAll(query, values);
		if (list != null && !list.isEmpty()) {
			return list.get(0);
		} else {
			return null;
		}
	}

	public T namedQuery(Transaction trans, String query, Object[] values)
			throws DAOException {
		List<T> list = this.namedQueryAll(trans, query, values);
		if (list != null && !list.isEmpty()) {
			return list.get(0);
		} else {
			return null;
		}
	}

	public List<T> namedQueryByPage(String query, Object[] values, int start,
			int size) throws DAOException {
		conn = this.getConnFromDS();
		try {
			return this.doNamedQuery(
					this.pagingSQL(query.toString(), start, size), values);
		} finally {
			this.closeRes(conn, null, null);
		}
	}

	public List<T> namedQueryByPage(Transaction trans, String query,
			Object[] values, int start, int size) throws DAOException {
		conn = getConnFromTrx(trans);
		return this.doNamedQuery(this.pagingSQL(query.toString(), start, size),
				values);
	}

	private String pagingSQL(String query, int start, int size) {
		return String.format(PAGE_SQL, query, start, size);
	}

	public List<T> namedQueryAll(String query, Object[] values)
			throws DAOException {
		conn = this.getConnFromDS();
		try {
			return this.doNamedQuery(query, values);
		} finally {
			this.closeRes(conn, null, null);
		}
	}

	public List<T> namedQueryAll(Transaction trans, String query,
			Object[] values) throws DAOException {
		conn = getConnFromTrx(trans);
		return this.doNamedQuery(query, values);
	}

	private List<T> doNamedQuery(String query, Object[] cond)
			throws DAOException {
		LOG.info("doNamedQuery：" + query);
		LOG.info("doNamedQuery:" + join(cond, ","));
		if (conn == null) {
			throw new DAOException("doNamedQuery.");
		}
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(query);
			for (int i = 0; i < cond.length; i++) {
				this.setPrepareStatement(ps, i + 1, cond[i]);
			}
			rs = ps.executeQuery();
			if (!rs.next()) {
				return null;
			}
			List<T> list = new ArrayList<T>();
			do {
				T t = clazz.newInstance();
				ResultSetMetaData meta = rs.getMetaData();
				for (int i = 0; i < meta.getColumnCount(); i++) {
					String fieldName = meta.getColumnName(i + 1);
					try {
						Field field = clazz.getDeclaredField(fieldName
								.toLowerCase());
						field.setAccessible(true);
						field.set(t, resolveField(rs, i + 1, field.getType()));
					} catch (NoSuchFieldException non) {
						LOG.debug("doNamedQuery."
								+ fieldName.toLowerCase());
					}
				}
				list.add(t);
			} while (rs.next());
			return list;
		} catch (InstantiationException ie) {
			throw new DAOException("doNamedQuery.", ie);
		} catch (IllegalAccessException iae) {
			throw new DAOException("doNamedQuery.", iae);
		} catch (SQLException se) {
			throw new DAOException("doNamedQuery.", se);
		} finally {
			this.closeRes(null, ps, rs);
		}
	}

	private void buildQuery(Condition[] conds, StringBuilder query,
			List<Object> values, OrderBy... orders) throws DAOException {
		if (query == null || values == null) {
			throw new IllegalArgumentException("buildQuery null.");
		}
		StringBuilder bys = new StringBuilder();
		if (orders != null && orders.length != 0) {
			boolean first = true;
			for (OrderBy order : orders) {
				if (first) {
					bys.append(" order by ").append(order.getName())
							.append(" ").append(order.getOrderBy());
					first = false;
				} else {
					bys.append(",").append(order.getName()).append(" ")
							.append(order.getOrderBy());
				}
			}
		}
		query.append("select * from ").append(this.tableName());
		if (conds != null && conds.length != 0) {
			boolean first = true;
			for (Condition cond : conds) {
				if (first) {
					query.append(" where ");
					first = false;
				} else {
					query.append(" and ");
				}
				query.append(cond.field).append(" = ? ");
				values.add(cond.value);
			}
		}
		query.append(bys);
	}

	public int updateItems(T item, Condition[] conds) throws DAOException {
		conn = this.getConnFromDS();
		try {
			return this.doUpdateItems(item, conds);
		} finally {
			this.closeRes(conn, null, null);
		}
	}

	public int updateItems(Transaction trans, T item, Condition[] conds)
			throws DAOException {
		conn = this.getConnFromDS();
		return this.doUpdateItems(item, conds);
	}

	public int namedUpdateItems(String update, Object[] values)
			throws DAOException {
		conn = this.getConnFromDS();
		return this.doNamedUpdateItems(update, values);
	}

	public int namedUpdateItems(Transaction trans, String update,
			Object[] values) throws DAOException {
		conn = this.getConnFromTrx(trans);
		return this.doNamedUpdateItems(update, values);
	}

	private int doUpdateItems(T item, Condition[] conds) throws DAOException {
		StringBuilder update = new StringBuilder();
		List<Object> values = new ArrayList<Object>();
		buildUpdateSQL(item, conds, update, values);
		return this.doNamedUpdateItems(update.toString(), values.toArray());
	}

	private int doNamedUpdateItems(String update, Object[] values)
			throws DAOException {
		LOG.info("doNamedUpdateItems：" + update);
		LOG.info("doNamedUpdateItems:" + join(values, ","));
		if (conn == null) {
			throw new DAOException("doNamedUpdateItems.");
		}
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(update);
			if (values != null && values.length != 0) {
				for (int i = 0; i < values.length; i++) {
					this.setPrepareStatement(ps, i + 1, values[i]);
				}
			}
			int ret = ps.executeUpdate();
			return ret;
		} catch (SQLException se) {
			throw new DAOException("doNamedUpdateItems.", se);
		} finally {
			this.closeRes(null, ps, rs);
		}
	}

	private void buildUpdateSQL(T item, Condition[] conds,
			StringBuilder update, List<Object> values) throws DAOException {
		if (update == null || update.length() != 0 || values == null
				|| !values.isEmpty()) {
			throw new IllegalArgumentException(
					"buildUpdateSQL.");
		}
		try {
			Field[] fields = clazz.getDeclaredFields();
			boolean first = true;
			for (Field field : fields) {
				if (Modifier.isStatic(field.getModifiers())) {
					continue;
				}
				field.setAccessible(true);
				String fieldName = field.getName();
				if (!"id".equals(fieldName)) {
					Object value = field.get(item);
					if (isPrimitive(field.getType())) {
						if (value instanceof Boolean) {
							values.add((Boolean) value ? "Y" : "N");
						} else {
							values.add(value);
						}
					} else {
						continue;
					}
				} else {
					continue;
				}
				if (first) {
					update.append("UPDATE ").append(this.tableName())
							.append(" SET " + fieldName).append(" = ?");
					first = false;
				} else {
					update.append(", ").append(fieldName).append(" = ?");
				}
			}
		} catch (IllegalAccessException illegal) {
			throw new DAOException("buildUpdateSQL.", illegal);
		}
		
		if (conds != null && conds.length != 0) {
			boolean first = true;
			for (Condition cond : conds) {
				if (first) {
					update.append(" where ");
					first = false;
				} else {
					update.append(" and ");
				}
				update.append(cond.field).append(" = ? ");
				values.add(cond.value);
			}
		}
	}

	public int deleteItem(Condition[] conds) throws DAOException {
		conn = this.getConnFromDS();
		try {
			return this.doDeleteItem(conds);
		} finally {
			closeRes(conn, null, null);
		}
	}

	public int deleteItem(Transaction trans, Condition[] conds)
			throws DAOException {
		conn = this.getConnFromTrx(trans);
		return this.doDeleteItem(conds);
	}

	private int doDeleteItem(Condition[] conds) throws DAOException {
		if (conn == null) {
			throw new DAOException("doDeleteItem.");
		}
		StringBuilder delete = new StringBuilder();
		List<Object> values = new ArrayList<Object>();
		buildDeleteSQL(conds, delete, values);
		LOG.info("doDeleteItem：" + delete.toString());
		LOG.info("doDeleteItem:" + values.toString());
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(delete.toString());
			for (int i = 0; i < values.size(); i++) {
				this.setPrepareStatement(ps, i + 1, values.get(i));
			}
			int ret = ps.executeUpdate();
			return ret;
		} catch (SQLException se) {
			throw new DAOException("doDeleteItem.", se);
		} finally {
			this.closeRes(null, ps, rs);
		}
	}

	private void buildDeleteSQL(Condition[] conds, StringBuilder remove,
			List<Object> values) throws DAOException {
		if (remove == null || remove.length() != 0 || values == null
				|| !values.isEmpty()) {
			throw new IllegalArgumentException(
					"buildDeleteSQL.");
		}
		remove.append("DELETE from ").append(this.tableName());
		
		if (conds != null && conds.length != 0) {
			boolean first = true;
			for (Condition cond : conds) {
				if (first) {
					remove.append(" where ");
					first = false;
				} else {
					remove.append(" and ");
				}
				remove.append(cond.field).append(" = ? ");
				values.add(cond.value);
			}
		}
	}

	private boolean isPrimitive(Class<?> clazz) {
		return clazz.isPrimitive() || clazz == String.class
				|| clazz == Date.class;
	}

	private void setPrepareStatement(PreparedStatement ps, int idx, Object value)
			throws SQLException {
		if (value == null) {
			ps.setNull(idx, Types.NULL);
		} else if (value instanceof String) {
			ps.setString(idx, (String) value);
		} else if (value instanceof Integer) {
			ps.setInt(idx, (Integer) value);
		} else if (value instanceof Long) {
			ps.setLong(idx, (Long) value);
		} else if (value instanceof Float) {
			ps.setFloat(idx, (Float) value);
		} else if (value instanceof Double) {
			ps.setDouble(idx, (Double) value);
		} else if (value instanceof Boolean) {
			ps.setString(idx, ((Boolean) value) ? "Y" : "N");
		} else if (value instanceof Date) {
			ps.setTimestamp(idx, new Timestamp(((Date) value).getTime()));
		} else if (value instanceof byte[]) {
			byte[] content = (byte[]) value;
			ByteArrayInputStream bais = new ByteArrayInputStream(content);
			ps.setBinaryStream(idx, bais, content.length);
		} else if (value instanceof Reader) {
			ps.setCharacterStream(idx, (Reader) value);
		} else {
			LOG.info(value.getClass().toString());
			throw new IllegalArgumentException("setPrepareStatement！");
		}
	}

	private Object resolveField(ResultSet rs, int index, Class<?> clazz)
			throws SQLException {
		if (String.class == clazz) {
			return rs.getString(index);
		}
		if (int.class == clazz) {
			return (Integer) rs.getInt(index);
		}
		if (float.class == clazz) {
			return (Float) rs.getFloat(index);
		}
		if (boolean.class == clazz) {
			return (Boolean) (BOOL_TRUE.equals(rs.getString(index).trim()));
		}
		if (Date.class == clazz) {
			java.sql.Timestamp time = rs.getTimestamp(index);
			return (time == null ? null : new Date(time.getTime()));
		}
		return null;
	}

	private Connection getConnFromDS() throws DAOException {
		try {
			ConnectionProvider ConnectionProvider = new ConnectionProvider();
			return ConnectionProvider.getConnection();
		} 
		catch (Exception e) {
			throw new DAOException("getConnFromDS.", e);
		}
	}

	private Connection getConnFromTrx(Transaction trans) throws DAOException {
		try {
			return trans.getConnection();
		} catch (SQLException e) {
			throw new DAOException("getConnFromTrx.", e);
		}
	}

	private String join(Object[] items, String split) {
		if (items == null || items.length == 0) {
			return null;
		}
		StringBuilder sb = new StringBuilder();
		boolean first = true;
		for (Object item : items) {
			if (first) {
				sb.append(item);
				first = false;
			} else {
				sb.append(split).append(item);
			}
		}
		return sb.toString();
	}

	private void closeRes(Connection conn, PreparedStatement ps, ResultSet rs) {
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				LOG.error("closeRes.", e);
			}
		}
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				LOG.error("closeRes.", e);
			}
		}
		if (ps != null) {
			try {
				ps.close();
			} catch (SQLException e) {
				LOG.error("closeRes.", e);
			}
		}
	}
}
